﻿Imports MySql.Data.MySqlClient
Public Class frmview_voyempty
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim inbtIndex As Integer
    Dim inbtIndex1 As Integer
    Dim count40 As Integer = 0
    Dim count20 As Integer = 0

    Private Sub DataGridViewX1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.CellContentClick

        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex
            DataGridViewX1.Rows(inbtIndex).Selected = True
            'MsgBox(DataGridViewX1.Rows(inbtIndex).Cells(9).Value)

        Catch ex As Exception

        End Try
        showgrid2()


    End Sub

    Private Sub frmview_voyempty_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture

        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage_empty where idvoyage_empty ='" & frmmain_voyempty.idvoyage & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                date_eta_penang.Text = mySqlReader("etdn")
                date_etd_penang.Text = mySqlReader("etan")
                date_krabi_eta.Text = mySqlReader("etds")
                txt_voyage.Text = mySqlReader("idvoyage")
                date_krabi_etd.Text = mySqlReader("etas")
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()



        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage_detail where voyempty ='" & txt_voyage.Text & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                If mySqlReader("emtype") = "40 HC" Then
                    count40 += CInt(mySqlReader("emno"))
                ElseIf mySqlReader("emtype") = "20 GP" Then
                    count20 += CInt(mySqlReader("emno"))
                End If
                
                DataGridViewX1.Rows.Add({mySqlReader("emship"), Trim(mySqlReader("emfor")), mySqlReader("embook"), mySqlReader("emno"), mySqlReader("emtype"), "", "", "", mySqlReader("emremark"), mySqlReader("idvoyage_detail")})



            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        TextBox1.Text = count20.ToString
        TextBox2.Text = count40.ToString


        TextBox3.Text = CInt(TextBox1.Text) * CInt(ComboBox1.Text)
        TextBox4.Text = CInt(TextBox2.Text) * CInt(ComboBox2.Text)
    End Sub
    Private Sub Column7_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Column7.Click

        If IsNumeric(DataGridViewX1.Rows(inbtIndex).Cells(9).Value) Then

        Else
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                mySqlCommand.Parameters.Clear()
                mySqlCommand.CommandText = "insert into voyage_detail ( emship, emfor, embook,emno,emtype,emremark,voyempty) values (@emship,@emfor,@embook,@emno,@emtype,@emremark,@voyempty)"
                mySqlCommand.Connection = mysql


                mySqlCommand.Parameters.AddWithValue("@emship", DataGridViewX1.Rows(inbtIndex).Cells(0).Value)
                mySqlCommand.Parameters.AddWithValue("@emfor", DataGridViewX1.Rows(inbtIndex).Cells(1).Value)
                mySqlCommand.Parameters.AddWithValue("@embook", DataGridViewX1.Rows(inbtIndex).Cells(2).Value)
                mySqlCommand.Parameters.AddWithValue("@emno", DataGridViewX1.Rows(inbtIndex).Cells(3).Value)
                mySqlCommand.Parameters.AddWithValue("@emtype", DataGridViewX1.Rows(inbtIndex).Cells(4).Value)
                mySqlCommand.Parameters.AddWithValue("@emremark", DataGridViewX1.Rows(inbtIndex).Cells(8).Value)
                mySqlCommand.Parameters.AddWithValue("@voyempty", Format(frmmain_voyempty.idvoyage, "000"))
                'mySqlCommand.Parameters.AddWithValue("@CTNCODEREPAIR", DataGridViewX1.Rows(inbtIndex).Cells(2).Value)
                'mySqlCommand.Parameters.AddWithValue("@CTNCODE", DataGridViewX1.Rows(inbtIndex).Cells(3).Value)
                'mySqlCommand.Parameters.AddWithValue("@CTNCODEETC", DataGridViewX1.Rows(inbtIndex).Cells(4).Value)
                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("บันทึกข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If

        showgrid()
    End Sub

    Private Sub DataGridViewX1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridViewX1.Click

    End Sub

    Private Sub DataGridViewX1_ClientSizeChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridViewX1.ClientSizeChanged

    End Sub

    Private Sub DataGridViewX1_RowHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridViewX1.RowHeaderMouseClick
        showgrid()
    End Sub

    Private Sub DataGridViewX1_RowLeave(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.RowLeave
        If e.ColumnIndex = 4 Then
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

        End If
    End Sub

    Private Sub Column8_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Column8.Click

        If IsNumeric(DataGridViewX1.Rows(inbtIndex).Cells(9).Value) Then

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                Dim commandText2 As String
                commandText2 = "UPDATE voyage_detail SET  emship = '" & DataGridViewX1.Rows(inbtIndex).Cells(0).Value & "' , emfor = '" & DataGridViewX1.Rows(inbtIndex).Cells(1).Value & "' , embook = '" & DataGridViewX1.Rows(inbtIndex).Cells(2).Value & "' , emno = '" & DataGridViewX1.Rows(inbtIndex).Cells(3).Value & "' , emtype = '" & DataGridViewX1.Rows(inbtIndex).Cells(4).Value & "' , emremark = '" & DataGridViewX1.Rows(inbtIndex).Cells(8).Value & "' WHERE idvoyage_detail = '" & DataGridViewX1.Rows(inbtIndex).Cells(9).Value & "'; "
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql
                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("แก้ไขข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            mysql.Close()



        End If

        showgrid()
    End Sub

    Private Sub Column9_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Column9.Click


        Dim respone As Object
        respone = MsgBox("ต้องการลบใช่หรือไม่", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try

                mySqlCommand.CommandText = "DELETE FROM voyage_detail where idvoyage_detail = '" & DataGridViewX1.Rows(inbtIndex).Cells(9).Value & "';"
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()


            Catch ex As Exception

                MsgBox(ex.ToString)
                Exit Sub
            End Try
            mysql.Close()


        End If
        showgrid()



    End Sub

    Public Sub showgrid()
        mysql.Close()

        count40 = 0
        count20 = 0
        DataGridViewX1.Rows.Clear()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage_detail where voyempty ='" & txt_voyage.Text & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                If mySqlReader("emtype") = "40 HC" Then
                    count40 += CInt(mySqlReader("emno"))
                ElseIf mySqlReader("emtype") = "20 GP" Then
                    count20 += CInt(mySqlReader("emno"))
                End If

                DataGridViewX1.Rows.Add({mySqlReader("emship"), Trim(mySqlReader("emfor")), mySqlReader("embook"), mySqlReader("emno"), mySqlReader("emtype"), "", "", "", mySqlReader("emremark"), mySqlReader("idvoyage_detail")})



            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        'TextBox1.Text = count20.ToString
        'TextBox2.Text = count40.ToString

        'If IsNumeric(CInt(TextBox1.Text)) And IsNumeric(CInt(ComboBox1.Text)) Then
        '    TextBox3.Text = CInt(TextBox1.Text) * CInt(ComboBox1.Text)
        'End If
        'If IsNumeric(CInt(TextBox2.Text)) And IsNumeric(CInt(ComboBox2.Text)) Then
        '    TextBox4.Text = CInt(TextBox2.Text) * CInt(ComboBox2.Text)
        'End If


    End Sub
    Public Sub showgrid2()
        mysql.Close()


        DataGridViewX2.Rows.Clear()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from empty_detail where idvoyage_detail ='" & DataGridViewX1.Rows(inbtIndex).Cells(9).Value & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())



                DataGridViewX2.Rows.Add({mySqlReader("detailcarno"), Trim(mySqlReader("detaildate")), mySqlReader("detailremark"), mySqlReader("detailremark2"), mySqlReader("detailremark3"), "", "", "", mySqlReader("idempty_detail")})



            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub

    Private Sub DataGridViewX2_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX2.CellContentClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex1 = e.RowIndex
            DataGridViewX2.Rows(inbtIndex1).Selected = True
            'MsgBox(DataGridViewX1.Rows(inbtIndex).Cells(9).Value)

        Catch ex As Exception

        End Try
    End Sub

    Private Sub G2SAVE_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles G2SAVE.Click

        If IsNumeric(DataGridViewX1.Rows(inbtIndex).Cells(8).Value) Then

        Else
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                mySqlCommand.Parameters.Clear()
                mySqlCommand.CommandText = "insert into empty_detail ( idvoyage_detail, detailcarno, detaildate,detailremark,detailremark2,detailremark3) values (@idvoyage_detail,@detailcarno,@detaildate,@detailremark,@detailremark2,@detailremark3)"
                mySqlCommand.Connection = mysql


                mySqlCommand.Parameters.AddWithValue("@idvoyage_detail", DataGridViewX1.Rows(inbtIndex).Cells(9).Value)
                mySqlCommand.Parameters.AddWithValue("@detailcarno", DataGridViewX2.Rows(inbtIndex1).Cells(0).Value)
                mySqlCommand.Parameters.AddWithValue("@detaildate", DataGridViewX2.Rows(inbtIndex1).Cells(1).Value)
                mySqlCommand.Parameters.AddWithValue("@detailremark", DataGridViewX2.Rows(inbtIndex1).Cells(2).Value)
                mySqlCommand.Parameters.AddWithValue("@detailremark2", DataGridViewX2.Rows(inbtIndex1).Cells(3).Value)
                mySqlCommand.Parameters.AddWithValue("@detailremark3", DataGridViewX2.Rows(inbtIndex1).Cells(4).Value)
                'mySqlCommand.Parameters.AddWithValue("@CTNCODEREPAIR", DataGridViewX1.Rows(inbtIndex).Cells(2).Value)
                'mySqlCommand.Parameters.AddWithValue("@CTNCODE", DataGridViewX1.Rows(inbtIndex).Cells(3).Value)
                'mySqlCommand.Parameters.AddWithValue("@CTNCODEETC", DataGridViewX1.Rows(inbtIndex).Cells(4).Value)
                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("บันทึกข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If

        showgrid2()


    End Sub

    Private Sub G2EDIT_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles G2EDIT.Click

        If IsNumeric(DataGridViewX2.Rows(inbtIndex1).Cells(8).Value) Then

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                Dim commandText2 As String
                commandText2 = "UPDATE empty_detail SET  detailcarno = '" & DataGridViewX2.Rows(inbtIndex1).Cells(0).Value & "' , detaildate = '" & DataGridViewX2.Rows(inbtIndex1).Cells(1).Value & "' , detailremark = '" & DataGridViewX2.Rows(inbtIndex1).Cells(2).Value & "' , detailremark2 = '" & DataGridViewX2.Rows(inbtIndex1).Cells(3).Value & "' , detailremark3 = '" & DataGridViewX2.Rows(inbtIndex1).Cells(4).Value & "' WHERE idempty_detail = '" & DataGridViewX2.Rows(inbtIndex1).Cells(8).Value & "'; "
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql
                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("แก้ไขข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            mysql.Close()



        End If

        showgrid2()


    End Sub


    Private Sub G2DEL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles G2DEL.Click
        Dim respone As Object
        respone = MsgBox("ต้องการลบใช่หรือไม่", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try

                mySqlCommand.CommandText = "DELETE FROM voyage_detail where idvoyage_detail = '" & DataGridViewX2.Rows(inbtIndex1).Cells(8).Value & "';"
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()


            Catch ex As Exception

                MsgBox(ex.ToString)
                Exit Sub
            End Try
            mysql.Close()


        End If
        showgrid()


    End Sub

    Private Sub DataGridViewX1_RowStateChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowStateChangedEventArgs) Handles DataGridViewX1.RowStateChanged

    End Sub


    Public Sub searchnew()

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        showgrid()
    End Sub

    'Private Sub ComboBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged

    'End Sub

    'Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
    '    showgrid()
    'End Sub

    'Private Sub ComboBox2_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedValueChanged

    'End Sub

    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        showgrid()
    End Sub
End Class